﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using BOHVote.Core;

namespace BOHVote.DAL
{
    public class UserProvide
    {
        Database db;
        public UserProvide()
        {
            db = DatabaseFactory.CreateDatabase();
        }

        #region << user >>
        public DataSet getUserList(string UserType,string strUserGovernID)
        {
            string SQLUser = "SELECT * FROM sys_user su LEFT JOIN sys_user_right sur ON  su.ID = sur.UserID LEFT JOIN Sys_right sr ON  sr.rightID = sur.rightID left join where 1=1 ";
            if(!string.IsNullOrEmpty(UserType))
            {
                SQLUser += " AND su.User_Type in (" + UserType + ")";
            }
            if (!string.IsNullOrEmpty(strUserGovernID))
            {
                SQLUser += " AND su.GovernID  = '" + strUserGovernID + "' ";
            }
            SQLUser += " order by su.GovernID,su.UnitID ";
            DbCommand dbCommand = db.GetSqlStringCommand(SQLUser);
            DataSet DataSetUserList = null;
            DataSetUserList = Utility.ExecuteDataSet(db,dbCommand);

            return DataSetUserList;
        }

        public DataSet SingleUser(string userID)
        {
            string SQLUser = "SELECT * FROM sys_user su LEFT JOIN sys_user_right sur ON  su.ID = sur.UserID LEFT JOIN Sys_right sr ON  sr.rightID = sur.rightID WHERE su.ID=" + userID;
            DbCommand dbCommand = db.GetSqlStringCommand(SQLUser);
            DataSet dst = null;
            dst = Utility.ExecuteDataSet(db, dbCommand);
            return dst;
        }


        public int DeleteUser(string UserID)
        {
            string SQLUser = "DELETE FROM sys_user WHERE ID= " + UserID ;
            string SQLAccess = "DELETE FROM sys_user_right WHERE ID= " + UserID;
            using (ITransactionManager transactionManager = new TransactionManager())
            {
                transactionManager.BeginTransaction();
                Database database = transactionManager != null ? transactionManager.Database : this.db;
                DbCommand dbCommand = database.GetSqlStringCommand(SQLUser);
                DbCommand dbCommandAccess = database.GetSqlStringCommand(SQLAccess);
                Utility.ExecuteNonQuery(database, dbCommand);
                Utility.ExecuteNonQuery(database, dbCommandAccess);
            }
            return 1;
        }

        public bool getUserVerify(string userName,string password)
        {
            string SQLVerify = "SELECT Count(1) FROM sys_user WHERE user_name = '" + userName + "' AND password = '" + password + "' AND Status = 'A'  ";
            DbCommand dbCommand = db.GetSqlStringCommand(SQLVerify);
            int countint = Convert.ToInt32(Utility.ExecuteScalar(db,dbCommand));
            if (countint == 0)
                return false;
            else
                return true;
        }

        public DataSet getUser(string userID, string password, string userType)
        {
            string SQLVerify = "SELECT su.*, sr.RightType,sr.rightCode,sr.RightName FROM sys_user su LEFT JOIN sys_user_right sur ON su.ID = sur.UserID LEFT JOIN Sys_right sr ON sur.RightID = sr.rightID WHERE su.user_ID = '" + userID + "' AND su.password = '" + password + "' AND su.Status = 'A'  ";
            if (userType == "P")
            {
                SQLVerify += " AND su.User_Type ='P' ";
            }
            else
                SQLVerify += " AND su.User_Type <> 'P' ";

            DbCommand dbCommand = db.GetSqlStringCommand(SQLVerify);
            DataSet dst = Utility.ExecuteDataSet(db, dbCommand);
            return dst;
        }

        public DataSet getUserListBySearch(string UserName)
        {
            return getUserListBySearch(UserName, "");
        }

        public string InsertUser(string UserCode,string UserName, string password, string UnitID, string GovernID, string user_type, string status, string userPhone, string Usermail)
        {
            string InsertSQL = " INSERT INTO sys_user(user_id,UnitID,GovernID,user_name,password,user_type,phone,mail,status)VALUES('" + UserCode + "','" + UnitID + "','" + GovernID + "','" + UserName + "','" + password + "','" + user_type + "','" + userPhone + "','" + Usermail + "','" + status + "') SELECT @@IDENTITY AS SEQUENCE ";
            DbCommand dbcommand = db.GetSqlStringCommand(InsertSQL);
            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }

        public int UpdateUser(string UserCode,string ID,string UserName, string password, string UnitID, string GovernID, string user_type, string status, string userPhone, string Usermail)
        {
            string UpdateSQL = "UPDATE sys_user SET User_id='" + UserCode + "', UnitID = '" + UnitID + "',GovernID = '" + GovernID + "',user_name = '" + UserName + "', password = '" + password + "',user_type = '" + user_type + "', phone = '" + userPhone + "', mail = '" + Usermail + "', status = '" + status + "'  WHERE ID= " + ID;

            DbCommand dbcommand = db.GetSqlStringCommand(UpdateSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public DataSet getUserListBySearch(string UserName,string UserType)
        {
            DataSet dst = null;
            string strSearchSQL = " SELECT * FROM sys_user WHERE 1=1 ";
            if (!String.IsNullOrEmpty(UserName))
            {
                strSearchSQL += "  and user_name like '%" + UserName + "%' ";
            }
            if (UserType.Trim().Length > 0 && UserType.Trim()!="-1")
            {
                strSearchSQL += " and user_type = '"+UserType+"' ";
            }

            strSearchSQL += " order by UnitID ";

            DbCommand dbCommand = db.GetSqlStringCommand(strSearchSQL);
            dst = Utility.ExecuteDataSet(db,dbCommand);

            return dst; 

        }

        public int ModifyPassword(string UserID, string Password)
        {
            string strSQL = "UPDATE sys_user SET password = '" + Password + "' WHERE ID = " + UserID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public DataSet getUserByUserCode(string UserCode)
        {
            string strSQL = "SELECT * FROM sys_user WHERE user_id = '" + UserCode + "' ";

            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            return  Utility.ExecuteDataSet(db, dbCommand);
        }

        #endregion

        #region <<  user right  >>

        public DataSet dstUserRightList(string strUserName,string strRightType,bool isShowAll)
        {
            string strSQL = " SELECT su.[ID] AS UserID,sur.ID AS SURID, sur.RightID AS RightID ,su.[User_Name] AS UserName,su.status AS UserStatus,su.User_Type AS UserType,sr.RightType,sr.rightName,sur.status AS RightStatus FROM sys_user su LEFT JOIN sys_user_right sur ON su.ID = sur.UserID LEFT JOIN Sys_right sr ON sr.rightID = sur.rightID WHERE 1=1";
            if (strUserName.Trim().Length > 0)
            {
                strSQL += " AND su.[User_Name] like '%" + strUserName + "%' ";
            }
            if (strRightType.Trim().Length > 0)
            {
                strSQL += " AND sur.RightID = '" + strRightType + "' "; 
            }

            if (!isShowAll)
            {
                strSQL += " AND sur.status = 'A' ";
            }

            strSQL += " ORDER BY sr.RightType DESC,su.GovernID,su.UnitID ";

            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            DataSet dstUserrightList = null;
            dstUserrightList = Utility.ExecuteDataSet(db, dbCommand);

            return dstUserrightList;
        }

        public DataSet dstUserRightByAccessID(string AccessID)
        {
            string strSQL = " SELECT su.[ID] AS UserID, sur.[ID] AS RightID ,su.[User_Name] AS UserName,su.status AS UserStatus,su.User_Type AS UserType,sr.RightType,sur.status AS RightStatus FROM sys_user su LEFT JOIN sys_user_right sur ON su.ID = sur.UserID LIFT JOIN Sys_Right sr on sr.RightID = sur.RightID WHERE 1=1 sur.[ID] = "+AccessID;
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            DataSet dstUserrightList = null;
            dstUserrightList = Utility.ExecuteDataSet(db, dbCommand);

            return dstUserrightList;
        }

        public int DeleteAccessByID(string AccessID)
        {
            string strSQL = "DELETE FROM sys_user_right WHERE ID = "+AccessID ;
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbCommand);
        }

        public int InsertUserAccess(string strUserID, string strRight,string status)
        {
            if (!String.IsNullOrEmpty(strRight))
            {
                string strSQL = "INSERT INTO sys_user_right(userID,RightID,status)VALUES('" + strUserID + "'," + strRight.Trim() + ",'" + status + "') ";
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                return Utility.ExecuteNonQuery(db, dbCommand);
            }
            else
                return -1;
        }

        public int UpdateUserAccess(string ID, string strRight,string UserID)
        {
            if (!String.IsNullOrEmpty(strRight))
            {
                string strSQL = "UPDATE sys_user_right SET RightID = '" + strRight + "' WHERE ID= " + ID + " AND UserID=" + UserID;

                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                return Utility.ExecuteNonQuery(db, dbCommand);
            }
            else
                return -1;
        }

        public string getUserAccessByUserID(string UserID,bool IsShowAll)
        {
            string strSQL = " SELECT RIGHTTYPE FROM sys_user_right WHERE 1=1 AND UserID = '"+UserID+"' ";
            if (!IsShowAll)
            {
                   strSQL += "  AND status  =  'A' ";
            }
            strSQL += "     ORDER BY CreateDate ";
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            return Convert.ToString(Utility.ExecuteScalar(db, dbCommand));
        }


        #endregion

        #region <<  right access  >>
        public DataSet dstGetAllRight()
        {
            DataSet dst = new DataSet();

            string strSQL = "SELECT * FROM Sys_right";
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            dst = Utility.ExecuteDataSet(db,dbCommand);
            return dst;
        }

        public int UpdateRight(string rightID,string rightName,string rightType,string rightCode)
        {
            string strSQL = "UPDATE Sys_right SET rightCode='" + rightCode + "',rightType='" + rightType + "', rightName='" + rightName + "' WHERE rightID =" + rightID + " ";
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbCommand);
        }

        public int InsertRight(string rightName, string rightCode, string rightType)
        {
            string strSQL = "Insert Into sys_right(rightCode,rightName,rightType)Values('" + rightCode + "','" + rightName + "','" + rightType + "') ";
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbCommand);
        }
        #endregion
    }
}
